--region [dbo].[AddCurrentIndicator]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddCurrentIndicator]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddCurrentIndicator]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddCurrentIndicator]
GO

CREATE PROCEDURE [dbo].[AddCurrentIndicator]
	@PRId int,
	@IndicatorId int,
	@DataValue float,
	@IndicatorHistoryId int
AS

INSERT INTO [PatientCurrentIndicator] (
	[PRId],
	[IndicatorId],
	[DataValue],
	[IndicatorHistoryId]
) VALUES (
	@PRId,
	@IndicatorId,
	@DataValue,
	@IndicatorHistoryId
)

GO
--endregion


--region [dbo].[UpdateCurrentIndicator]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateCurrentIndicator]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateCurrentIndicator]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateCurrentIndicator]
GO

CREATE PROCEDURE [dbo].[UpdateCurrentIndicator]
	@PRId int,
	@IndicatorId int,
	@DataValue float,
	@IndicatorHistoryId int
AS

UPDATE [PatientCurrentIndicator] SET
	[DataValue] = @DataValue,
	[IndicatorHistoryId] = @IndicatorHistoryId
WHERE
	[PRId] = @PRId AND
	[IndicatorId] = @IndicatorId

GO
--endregion


--region [dbo].[DelCurrentIndicator]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelCurrentIndicator]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelCurrentIndicator]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelCurrentIndicator]
GO

CREATE PROCEDURE [dbo].[DelCurrentIndicator]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[PatientCurrentIndicator]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetCurrentIndicator]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCurrentIndicator]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCurrentIndicator]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCurrentIndicator]
GO

CREATE PROCEDURE [dbo].[GetCurrentIndicator]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	''Id'' = [PatientIndicatorHistory].[Id],
	''PRId'' = [PatientCurrentIndicator].[PRId],
	''IndicatorId'' = [PatientCurrentIndicator].[IndicatorId],
	''DataValue'' = [PatientCurrentIndicator].[DataValue],
	[RecordDate],
	[UpdateDate]
FROM
	[PatientCurrentIndicator], [PatientIndicatorHistory]
WHERE
	[PatientCurrentIndicator].[IndicatorHistoryId] = [PatientIndicatorHistory].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetCurrentIndicatorList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCurrentIndicatorList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCurrentIndicatorList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCurrentIndicatorList]
GO

CREATE PROCEDURE [dbo].[GetCurrentIndicatorList]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	''Id'' = [PatientIndicatorHistory].[Id],
	''PRId'' = [PatientCurrentIndicator].[PRId],
	''IndicatorId'' = [PatientCurrentIndicator].[IndicatorId],
	[IndicatorName],
	''DataValue'' = [PatientCurrentIndicator].[DataValue],
	[Measurementunits],
	[SurveyId],
	[RecordDate],
	[UpdateDate]
FROM
	[PatientCurrentIndicator], [PatientIndicatorHistory], [LabCheck]
WHERE
	[PatientCurrentIndicator].[IndicatorHistoryId] = [PatientIndicatorHistory].[Id] AND
	[PatientCurrentIndicator].[IndicatorId] = [LabCheck].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetCurrentIndicatorCount]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCurrentIndicatorCount]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCurrentIndicatorCount]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCurrentIndicatorCount]
GO

CREATE PROCEDURE [dbo].[GetCurrentIndicatorCount]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id)
FROM
	[PatientCurrentIndicator]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion



--region [dbo].[AddIndicatorHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddIndicatorHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddIndicatorHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddIndicatorHistory]
GO

CREATE PROCEDURE [dbo].[AddIndicatorHistory]
	@PRId int,
	@IndicatorId int,
	@SurveyId int,
	@DataValue float,
	@RecordDate datetime,
	@UpdateDate datetime,
	@Remark nvarchar(2000),
	@id int OUTPUT
AS

INSERT INTO [PatientIndicatorHistory] (
	[PRId],
	[IndicatorId],
	[SurveyId],
	[DataValue],
	[RecordDate],
	[UpdateDate],
	[Remark]
) VALUES (
	@PRId,
	@IndicatorId,
	@SurveyId,
	@DataValue,
	@RecordDate,
	@UpdateDate,
	@Remark
)

SET @id = SCOPE_IDENTITY()

GO
--endregion


--region [dbo].[UpdateIndicatorHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateIndicatorHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateIndicatorHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateIndicatorHistory]
GO

CREATE PROCEDURE [dbo].[UpdateIndicatorHistory]
	@Id int,
	@PRId int,
	@IndicatorId int,
	@SurveyId int,
	@DataValue float,
	@RecordDate datetime,
	@UpdateDate datetime,
	@Remark nvarchar(2000)
AS

UPDATE [PatientIndicatorHistory] SET
	[SurveyId] = @SurveyId,
	[DataValue] = @DataValue,
	[RecordDate] = @RecordDate,
	[UpdateDate] = @UpdateDate,
	[Remark] = @Remark
WHERE
	[PRId] = @PRId AND [IndicatorId] = @IndicatorId AND [Id] = @Id 
GO
--endregion


--region [dbo].[DelIndicatorHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelIndicatorHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelIndicatorHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelIndicatorHistory]
GO

CREATE PROCEDURE [dbo].[DelIndicatorHistory]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[PatientIndicatorHistory]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetIndicatorHistory]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetIndicatorHistory]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetIndicatorHistory]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetIndicatorHistory]
GO

CREATE PROCEDURE [dbo].[GetIndicatorHistory]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	''Id'' = [PatientIndicatorHistory].[Id],
	[PRId],
	[IndicatorId],
	[IndicatorName],
	[SurveyId],
	[DataValue],
	[Measurementunits],
	[RecordDate],
	[UpdateDate],
	[Remark]
FROM
	[PatientIndicatorHistory], [LabCheck]
WHERE
	[PatientIndicatorHistory].[IndicatorId] = [LabCheck].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetIndicatorHistoryList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetIndicatorHistoryList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetIndicatorHistoryList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetIndicatorHistoryList]
GO

CREATE PROCEDURE [dbo].[GetIndicatorHistoryList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [PatientIndicatorHistory]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	''Id'' = [PatientIndicatorHistory].[Id],
	[PRId],
	[IndicatorId],
	[IndicatorName],
	[SurveyId],
	[DataValue],
	[Measurementunits],
	[RecordDate],
	[UpdateDate],
	[Remark]
FROM
	[PatientIndicatorHistory], [LabCheck]
WHERE
	[PatientIndicatorHistory].[IndicatorId] = [LabCheck].[Id]
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [PatientIndicatorHistory].[Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [PatientIndicatorHistory] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' AND ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetSurveyHistoryIndicatorList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSurveyHistoryIndicatorList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSurveyHistoryIndicatorList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSurveyHistoryIndicatorList]
GO

CREATE PROCEDURE [dbo].[GetSurveyHistoryIndicatorList]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	''Id'' = [PatientIndicatorHistory].[Id],
	[PRId],
	[IndicatorId],
	[IndicatorName],
	[SurveyId],
	[DataValue],
	[Measurementunits],
	[RecordDate],
	[UpdateDate],
	[Remark]
FROM
	[PatientIndicatorHistory], [LabCheck]
WHERE
	[PatientIndicatorHistory].[IndicatorId] = [LabCheck].[Id] AND
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetIndicatorHistoryCount]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetIndicatorHistoryCount]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetIndicatorHistoryCount]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetIndicatorHistoryCount]
GO

CREATE PROCEDURE [dbo].[GetIndicatorHistoryCount]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id)
FROM
	[PatientIndicatorHistory]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


